using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace FrameWorkSystem.BusinessLayer.DataLayer
{
	/// <summary>
	/// Data access layer class for Sys_UserPermission
	/// </summary>
	class Sys_UserPermissionSql : DataLayerBase 
	{

        #region Constructor

		/// <summary>
		/// Class constructor
		/// </summary>
		public Sys_UserPermissionSql()
		{
			// Nothing for now.
		}

        #endregion

        #region Public Methods

        /// <summary>
        /// insert new row in the table
        /// </summary>
		/// <param name="businessObject">business object</param>
		/// <returns>true of successfully insert</returns>
		public bool Insert(Sys_UserPermission businessObject)
		{
			SqlCommand	sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "INSERT [Sys_UserPermission]([UserName],[FuncName],[IsEnable],[IsVisible],[TypeOfFunc])VALUES(@UserName,@FuncName,@IsEnable,@IsVisible,@TypeOfFunc)";
			sqlCommand.CommandType = CommandType.Text;

			// Use connection object of base class
			sqlCommand.Connection = MainConnection;

			try
			{
                
				sqlCommand.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, 30, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.UserName));
				sqlCommand.Parameters.Add(new SqlParameter("@FuncName", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.FuncName));
				sqlCommand.Parameters.Add(new SqlParameter("@IsEnable", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.IsEnable));
				sqlCommand.Parameters.Add(new SqlParameter("@IsVisible", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.IsVisible));
				sqlCommand.Parameters.Add(new SqlParameter("@TypeOfFunc", SqlDbType.VarChar, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TypeOfFunc));

								
				MainConnection.Open();
				
				sqlCommand.ExecuteNonQuery();
                
				return true;
			}
			catch(Exception ex)
			{				
				throw new Exception("Sys_UserPermission::Insert::Error occured.", ex);
			}
			finally
			{			
				MainConnection.Close();
				sqlCommand.Dispose();
			}
		}

         /// <summary>
        /// update row in the table
        /// </summary>
        /// <param name="businessObject">business object</param>
        /// <returns>true for successfully updated</returns>
        public bool Update(Sys_UserPermission businessObject)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "UPDATE [Sys_UserPermission]SET [IsEnable] = @IsEnable, [IsVisible] = @IsVisible, [TypeOfFunc] = @TypeOfFunc WHERE  [UserName] = @UserName AND [FuncName] = @FuncName";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
				sqlCommand.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, 30, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.UserName));
				sqlCommand.Parameters.Add(new SqlParameter("@FuncName", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.FuncName));
				sqlCommand.Parameters.Add(new SqlParameter("@IsEnable", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.IsEnable));
				sqlCommand.Parameters.Add(new SqlParameter("@IsVisible", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.IsVisible));
				sqlCommand.Parameters.Add(new SqlParameter("@TypeOfFunc", SqlDbType.VarChar, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TypeOfFunc));

                
                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Sys_UserPermission::Update::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }
        }

        /// <summary>
        /// Select by primary key
        /// </summary>
        /// <param name="keys">primary keys</param>
        /// <returns>Sys_UserPermission business object</returns>
        public Sys_UserPermission SelectByPrimaryKey(Sys_UserPermissionKeys keys)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT * FROM [dbo].[Sys_UserPermission]WHERE [UserName] = @UserName AND [FuncName] = @FuncName";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {


                
                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                if (dataReader.Read())
                {
                    Sys_UserPermission businessObject = new Sys_UserPermission();

                    PopulateBusinessObjectFromReader(businessObject, dataReader);

                    return businessObject;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Sys_UserPermission::SelectByPrimaryKey::Error occured.", ex);
            }
            finally
            {             
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }
        public List<Sys_UserPermission> GetNoPermission(string uName, bool permis, FrameWorkSystem.PhanQuyen.TypeOfFunc _type)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT * FROM dbo.Sys_UserPermission WHERE UserName = @UserName AND IsEnable = @Permission AND TypeOfFunc = @TypeOfFunc";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            string TypeOfFunc = "TV";
            if (_type == FrameWorkSystem.PhanQuyen.TypeOfFunc.Menu)
                TypeOfFunc = "MN";
            try
            {
                sqlCommand.Parameters.Add("@UserName", SqlDbType.VarChar,30).Value = uName;
                sqlCommand.Parameters.Add("@Permission", SqlDbType.Bit).Value = permis;
                sqlCommand.Parameters.Add("@TypeOfFunc", SqlDbType.VarChar, 30).Value = TypeOfFunc;

                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {
                throw new Exception("Sys_UserPermission::SelectAll::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }
        }
        public List<Sys_UserPermission> SelectAll()
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT * FROM [Sys_UserPermission]";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {                
                throw new Exception("Sys_UserPermission::SelectAll::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        public List<Sys_UserPermission> GetAllByType(PhanQuyen.TypeOfFunc type, string Uname)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT * FROM Sys_UserPermission WHERE UserName = @UserName AND TypeOfFunc = @TypeOfFunc";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                sqlCommand.Parameters.Add(new SqlParameter("@UserName",SqlDbType.VarChar, 50)).Value =Uname;
                sqlCommand.Parameters.Add(new SqlParameter("@TypeOfFunc",SqlDbType.VarChar,4)).Value = type == FrameWorkSystem.PhanQuyen.TypeOfFunc.Menu?"MN":"TV";

                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {
                throw new Exception("Sys_UserPermission::SelectByField::Error occured.", ex);
            }
            finally
            {

                MainConnection.Close();
                sqlCommand.Dispose();
            }
        }
        public List<Sys_UserPermission> SelectByField(string fieldName, object value)
        {

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT [UserName], [FuncName], [IsEnable], [IsVisible], [TypeOfFunc] FROM [dbo].[Sys_UserPermission] WHERE [' + @FieldName  + '] = ''' + @Value + '''";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                sqlCommand.Parameters.Add(new SqlParameter("@FieldName", fieldName));
                sqlCommand.Parameters.Add(new SqlParameter("@Value", value));

                MainConnection.Open();
                
                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {
                throw new Exception("Sys_UserPermission::SelectByField::Error occured.", ex);
            }
            finally
            {

                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Delete by primary key
        /// </summary>
        /// <param name="keys">primary keys</param>
        /// <returns>true for successfully deleted</returns>
        public bool Delete(Sys_UserPermissionKeys keys)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "DELETE FROM [dbo].[Sys_UserPermission] WHERE 	[UserName] = @UserName AND  [FuncName] = @FuncName";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;
            sqlCommand.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, 30, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.UserName));
            sqlCommand.Parameters.Add(new SqlParameter("@FuncName", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.FuncName));
            try
            {

                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Sys_UserPermission::DeleteByKey::Error occured.", ex);
            }
            finally
            {                
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Delete records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>true for successfully deleted</returns>
        public bool DeleteByField(string fieldName, object value)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "DELETE FROM [Sys_UserPermission] WHERE [" + fieldName + "] = N'" + value.ToString() + "'";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {                
                
                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();

                return true;

            }
            catch (Exception ex)
            {                
                throw new Exception("Sys_UserPermission::DeleteByField::Error occured.", ex);
            }
            finally
            {             
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        #endregion

        #region Private Methods

        /// <summary>
        /// Populate business object from data reader
        /// </summary>
        /// <param name="businessObject">business object</param>
        /// <param name="dataReader">data reader</param>
        internal void PopulateBusinessObjectFromReader(Sys_UserPermission businessObject, IDataReader dataReader)
        {


				businessObject.UserName = dataReader.GetString(dataReader.GetOrdinal(Sys_UserPermission.Sys_UserPermissionFields.UserName.ToString()));

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Sys_UserPermission.Sys_UserPermissionFields.FuncName.ToString())))
				{
					businessObject.FuncName = dataReader.GetString(dataReader.GetOrdinal(Sys_UserPermission.Sys_UserPermissionFields.FuncName.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Sys_UserPermission.Sys_UserPermissionFields.IsEnable.ToString())))
				{
					businessObject.IsEnable = dataReader.GetBoolean(dataReader.GetOrdinal(Sys_UserPermission.Sys_UserPermissionFields.IsEnable.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Sys_UserPermission.Sys_UserPermissionFields.IsVisible.ToString())))
				{
					businessObject.IsVisible = dataReader.GetBoolean(dataReader.GetOrdinal(Sys_UserPermission.Sys_UserPermissionFields.IsVisible.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Sys_UserPermission.Sys_UserPermissionFields.TypeOfFunc.ToString())))
				{
					businessObject.TypeOfFunc = dataReader.GetString(dataReader.GetOrdinal(Sys_UserPermission.Sys_UserPermissionFields.TypeOfFunc.ToString()));
				}


        }

        /// <summary>
        /// Populate business objects from the data reader
        /// </summary>
        /// <param name="dataReader">data reader</param>
        /// <returns>list of Sys_UserPermission</returns>
        internal List<Sys_UserPermission> PopulateObjectsFromReader(IDataReader dataReader)
        {

            List<Sys_UserPermission> list = new List<Sys_UserPermission>();

            while (dataReader.Read())
            {
                Sys_UserPermission businessObject = new Sys_UserPermission();
                PopulateBusinessObjectFromReader(businessObject, dataReader);
                list.Add(businessObject);
            }
            return list;

        }

        #endregion

	}
}
